/*
 *所有关于Month_Asses类的业务代码应在此处编写
*可使用repository.调用常用方法，获取EF/Dapper等信息
*如果需要事务请使用repository.DbContextBeginTransaction
*也可使用DBServerProvider.手动获取数据库相关信息
*用户信息、权限、角色等使用UserContext.Current操作
*Month_AssesService对增、删、改查、导入、导出、审核业务代码扩展参照ServiceFunFilter
*/
using VOL.Core.BaseProvider;
using VOL.Core.Extensions.AutofacManager;
using VOL.Entity.DomainModels;
using System.Linq;
using VOL.Core.Utilities;
using System.Linq.Expressions;
using VOL.Core.Extensions;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.AspNetCore.Http;
using VOL.Dhj.IRepositories;
using VOL.Core.DBManager;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Information;
using VOL.Dhj.Repositories;
using System.Runtime.Intrinsics.X86;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Logical;
using SkiaSharp;
using System;
using OfficeOpenXml.FormulaParsing.Excel.Functions.DateTime;
using System.Reflection;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using VOL.Core.ManageUser;

namespace VOL.Dhj.Services
{
    public partial class Month_AssesService
    {
        private readonly IHttpContextAccessor _httpContextAccessor;
        private readonly IMonth_AssesRepository _repository;//访问数据库


        [ActivatorUtilitiesConstructor]
        public Month_AssesService(
            IMonth_AssesRepository dbRepository,
            IHttpContextAccessor httpContextAccessor,
            IAttendance_RecordsRepository attendance_recordsRepository
            )
        : base(dbRepository)
        {
            _httpContextAccessor = httpContextAccessor;
            _repository = dbRepository;
            //多租户会用到这init代码，其他情况可以不用
            //base.Init(dbRepository);
        }
        
        public void DailyUpdates(string value2)
        {

            var dapper = DBServerProvider.SqlDapper;
            try
            {
                dapper.BeginTrans();

                string year = value2[0..4];//年
                string month = value2[5..7];//月

                String months = month + "月";

                string nowDate = string.Format("{0:yyyy-MM}", value2) + "%";

                //根据月份把每日表中数据取出来
                string sql = "select * from Attendance_Records WHERE CONVERT(VARCHAR, Attendance_date, 23) LIKE @nowDate";
                List<Month_Asses> listMonth = dapper.QueryList<Month_Asses>(sql, new { nowDate });

                
                //从用户表中将所有用户的基础信息放入月度表
                string sqlPerson = "SELECT * FROM Division_Person";
                List<Division_Person> listPerson = dapper.QueryList<Division_Person>(sqlPerson, true);

                foreach (Division_Person item in listPerson)
                {
                    //todo
                    string sql0 = "SELECT* from Month_Asses where Person = @person AND Month = @months";
                    List<Month_Asses> listPerson01 = dapper.QueryList<Month_Asses>(sql0, new { person = item.BusinessManager, months });//从每月签到表中查看当月该用户是否存入
                    int mon;
                    //如果没有存入，新增
                    if (listPerson01.Count <= 0)
                    {
                        if (listMonth.Count >= 0)
                        {
                            Month_Asses m = new Month_Asses();
                            m.Division = item.Division;//部门+
                            m.Person = item.BusinessManager;//用户名
                            m.Month = month + '月';//月份
                            mon = month.ToInt();                          

                            //从每日表中查找最大的日期
                            string sqlTime = "SELECT MAX(Attendance_date) AS times FROM Attendance_Records WHERE CONVERT(VARCHAR, Attendance_date, 23) LIKE @nowDate";
                            var result = dapper.QueryDynamicFirst(sqlTime, new { nowDate });
                            DateTime? maxAttendanceDate = result?.times;
                            int day = maxAttendanceDate.ToDateTime().Day; //最大的天数                         
                                                          
                                for (int Day = 1; Day <= day; Day++)
                                {
                                    //31天  
                                    string data = "未打卡"; // 假设要存放的数据是 day * 10
                                    m.SetDayData(Day, data);
                                }                                                                                                                                                                            

                            _repository.Add(m, true);
                        }
                    }

                        //根据业务经理名将一整个月的数据导入月度表
                        //查询每日签到表中每一个业务经理该月的考勤数据
                        string sql1 = "SELECT * FROM Attendance_Records WHERE Manager = @businessManager and MONTH(Attendance_date) = @month and YEAR(Attendance_date) = @year order by Attendance_date";
                        List<Attendance_Records> listPerson1 = dapper.QueryList<Attendance_Records>(sql1, new { item.BusinessManager, month ,year});
                        int total = 0, askForLeave = 0, travel = 0, attendance = 0, absence = 0, rest = 0;
                        foreach (Attendance_Records item1 in listPerson1)
                        {
                            int day = item1.Attendance_date.ToDateTime().Day;
                            string sss = "Day" + day;
                            string classification = null;

                            if (item1.Classify == 1)
                            {
                                classification = "请假";
                                askForLeave++;
                            }
                            else if (item1.Classify == 2 || item1.Classify == 3)
                            {
                                total++;
                                if (item1.Classify == 2)
                                {
                                    classification = "出差";
                                    travel++;
                                }
                                else if ((item1.Classify == 3))
                                {
                                    classification = "出勤";
                                    attendance++;
                                }

                            }
                            else if (item1.Classify == 4)
                            {
                                classification = "未打卡";
                                absence++;
                            }
                            else if (item1.Classify == 5)
                            {
                                classification = "休息";
                                rest++;
                            }

                            string sql2 = $"Update Month_Asses set {sss}=@value WHERE Person = @businessManager and Month = @msmonth";
                            dapper.ExcuteNonQuery(sql2, new { value = classification, item.BusinessManager, msmonth = month + '月' });
                        }
                        string sql22 = $"Update Month_Asses set Total=@total,AskForLeave=@askForLeave,Travel=@travel,attendance=@attendance,Absence=@absence,Rest=@rest WHERE Person = @businessManager and Month = @msmonth";
                        dapper.ExcuteNonQuery(sql22, new { total, askForLeave, travel, attendance, absence, rest, item.BusinessManager, msmonth = month + '月' });
                    }
                                       

                

                dapper.Commit();
            }
            catch (Exception ex)
            {
                dapper.Rollback();
                throw new Exception(ex.Message);
            }
                                
        }

        //查询
        public override PageGridData<Month_Asses> GetPageData(PageDataOptions options)
        {

            QueryRelativeExpression = (IQueryable<Month_Asses> queryable) =>
            {
                var dapper = DBServerProvider.SqlDapper;
                //获取用户信息
                int userAccount = UserContext.Current.UserInfo.Role_Id;
                string userTrueName = UserContext.Current.UserInfo.UserTrueName;//用户权限等级
                string userName = UserContext.Current.UserInfo.UserName;//登录的用户名

                //Console.WriteLine("用户信息" + userName);


                //获取用户权限
                Permissions a = UserContext.Current.GetPermissions(x => x.TableName.ToLower() == "attendance_records");

                Boolean pw = false;//初始设置为没有访问权限
                Array.ForEach(a.UserAuthArr, item =>
                {
                    //当用户存在私人查询权限时，只能查到用户自己的信息
                    if (item.Equals("public_query"))
                    {
                        pw = true;//开放权限                                               
                    }
                    else
                    {
                        if (!pw)
                        {
                            pw = false;
                        }

                    }
                });

                if (pw == true)
                {
                    //有权限，查询全部的用户信息
                    string sql = "SELECT * from Month_Asses ";
                    List<Month_Asses> list11 = dapper.QueryList<Month_Asses>(sql, true);
                }
                else
                {
                    //没有权限，只能查询用户本身的信息                   
                    //queryable = queryable.Where(x => x.StaffAccount == userName);

                    queryable = queryable.Where(x => x.Person == "孔凡军");
                }

                return queryable;
            };




            return base.GetPageData(options);
        }

    }
}
